package com.ymatou.iapi.trading.sellerquery2.parameter.sp;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ymttest.database.sqlwapper.DefaultWapper;
import com.ymttest.utils.DataUtil;

public class Sp_GetBondedProductInfo {
	private Integer sellerId;

	private int considerAcceptTime = 0;
	private String now;
	private Integer bondedTimeout;

	private List<Integer> tradingStatus;

	private int considerPaidTime = 0;
	private String startDate;
	private String endDate;

	private int considerCatalogStatus = 0;
	private Integer catalogStatus;
	private Integer catalogStatus2;

	private int considerBondedArea = 0;
	private Integer bondedArea;

	private int considerBondedNoResult = 0;

	private int considerIdPic = 0;
	private int needIdPic = 0;
	private int considerIdPicAddTime = 0;

	private int outputOrdersExistOnly = 0;

	public void setSellerId(Integer sellerId) {
		this.sellerId = sellerId;
	}

	public void setConsiderAcceptTime(int considerAcceptTime) {
		this.considerAcceptTime = considerAcceptTime;
	}

	public void setNow(String now) {
		this.now = now;
	}

	public void setBondedTimeout(Integer bondedTimeout) {
		this.bondedTimeout = bondedTimeout;
	}


	public void setTradingStatus(List<Integer> tradingStatus) {
		this.tradingStatus = tradingStatus;
	}

	public void setConsiderPaidTime(int considerPaidTime) {
		this.considerPaidTime = considerPaidTime;
	}

	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}

	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}

	public void setConsiderCatalogStatus(int considerCatalogStatus) {
		this.considerCatalogStatus = considerCatalogStatus;
	}

	public void setCatalogStatus(Integer catalogStatus) {
		this.catalogStatus = catalogStatus;
	}

	public void setCatalogStatus2(Integer catalogStatus2) {
		this.catalogStatus2 = catalogStatus2;
	}

	public void setConsiderBondedArea(int considerBondedArea) {
		this.considerBondedArea = considerBondedArea;
	}

	public void setBondedArea(Integer bondedArea) {
		this.bondedArea = bondedArea;
	}

	public void setConsiderBondedNoResult(int considerBondedNoResult) {
		this.considerBondedNoResult = considerBondedNoResult;
	}

	public void setConsiderIdPic(int considerIdPic) {
		this.considerIdPic = considerIdPic;
	}

	public void setNeedIdPic(int needIdPic) {
		this.needIdPic = needIdPic;
	}

	public void setConsiderIdPicAddTime(int considerIdPicAddTime) {
		this.considerIdPicAddTime = considerIdPicAddTime;
	}

	public void setOutputOrdersExistOnly(int outputOrdersExistOnly) {
		this.outputOrdersExistOnly = outputOrdersExistOnly;
	}

	@SuppressWarnings("rawtypes")
	public Map<String, List<Map>> execute() {
		Map<String, List<Map>> result = new HashMap<String, List<Map>>();
		DefaultWapper db = new DefaultWapper();

		StringBuilder order = new StringBuilder();
		DataUtil.SQL.declareVarOfSp("i", order, "sellerId", sellerId);
		DataUtil.SQL.declareVarOfSp("b", order, "considerAcceptTime", considerAcceptTime);
		DataUtil.SQL.declareVarOfSp("dt", order, "now", now);
		DataUtil.SQL.declareVarOfSp("i", order, "bondedTimeout", bondedTimeout);
		DataUtil.SQL.declareVarOfSp("b", order, "considerPaidTime", considerPaidTime);
		DataUtil.SQL.declareVarOfSp("dt", order, "startDate", startDate);
		DataUtil.SQL.declareVarOfSp("dt", order, "endDate", endDate);
		DataUtil.SQL.declareVarOfSp("b", order, "considerBondedArea", considerBondedArea);
		DataUtil.SQL.declareVarOfSp("i", order, "bondedArea", bondedArea);
		DataUtil.SQL.declareVarOfSp("b", order, "considerCatalogStatus", considerCatalogStatus);
		DataUtil.SQL.declareVarOfSp("i", order, "catalogStatus", catalogStatus);
		DataUtil.SQL.declareVarOfSp("i", order, "catalogStatus2", catalogStatus2);
		DataUtil.SQL.declareVarOfSp("b", order, "considerBondedNoResult", considerBondedNoResult);
		DataUtil.SQL.declareVarOfSp("b", order, "considerIdPic", considerIdPic);
		DataUtil.SQL.declareVarOfSp("b", order, "needIdPic", needIdPic);
		DataUtil.SQL.declareVarOfSp("b", order, "considerIdPicAddTime", considerIdPicAddTime);
		DataUtil.SQL.declareVarOfSp("b", order, "outputOrdersExistOnly", outputOrdersExistOnly);
		
		order.append(" declare @orders table(id int, [uid] int, receiver varchar(50), del smallint default 0);");
		order.append(" declare @next int = 1;");
		order.append(" declare @toBeDeleted int = 0;");
		order.append(" declare @withoutPic smallint;");

		order.append(" insert into @orders(id, [uid], receiver)");
		order.append(" select id, [uid], receiver from(");
		order.append("  select ROW_NUMBER() over(partition by o.iOrderId order by o.iOrderId) as n, o.iOrderId as id, o.iUserId as [uid], o.sReceivePerson as [receiver]");
		order.append("  from Ymt_Orders(nolock) o join Ymt_OrderInfo(nolock) i on o.iBuyerId = @sellerId and o.iOrderId = i.iOrderId");
		order.append(" and (@considerAcceptTime != 1 or @considerAcceptTime = 1 and o.dAcceptTime is not null and datediff(n, o.dAcceptTime, @now) > @bondedTimeout)");
		if (!DataUtil.isNullOrEmpty(tradingStatus)) {
			order.append(" and o.iTradingStatus in "
					+ DataUtil.toInOfSql(tradingStatus));
		}
		order.append(" and (@considerPaidTime != 1 or @considerPaidTime = 1 and o.dPaidTime >= @startDate and o.dPaidTime < @endDate)");
		order.append(" and (@considerBondedArea != 1 or @considerBondedArea = 1 and i.iBondedArea = @bondedArea)");
		order.append(" and (@considerCatalogStatus != 1 or @considerCatalogStatus = 1 and (i.iCatalogStatus = @catalogStatus or i.iCatalogStatus = @catalogStatus2))");
		order.append(" and i.iCatalogType != 2");
		order.append(" ) t where t.n = 1;");


		String s = "if @@ROWCOUNT > 0 begin"
				+ "		set @next = 0;"
				+ " 		if @considerBondedNoResult > 0 begin"
				+ "			update @orders set del = 1 where id in(select iOrderId from Ymt_BondedResult(nolock) where iOrderId in (select id from @orders))"
				+ " 			set @toBeDeleted = @@ROWCOUNT;"
				+ " 			set @next = @toBeDeleted;"
				+ " 		end"
				+ " 		if @considerIdPic = 1 begin"
				+ " 			set @withoutPic = case @needIdPic when 1 then -1 else 1 end"
				+ " 			update o set o.del = @withoutPic"
				+ " 			from @orders o join ymt_idpic(nolock) p on o.[uid] = p.iUserId and o.receiver = p.sName and o.del != 1"
				+ " 			and (@considerIdPicAddTime != 1 or @considerIdPicAddTime = 1 and p.dAddTime >= @startDate and p.dAddTime < @endDate)"
				+ " 			set @toBeDeleted = @@ROWCOUNT;"
				+ "  			if @toBeDeleted > 0 begin"
				+ "  				if @needIdPic = 1 delete from @orders where del = 0;"
				+ " 				else set @next += @toBeDeleted;" 
				+ " 			end" 
				+ " 		end"
				+ " 		if @next > 0  delete from @orders where del = 1" 
				+ " end";
		order.append(s);
		
		order.append(" set @next = 0;");
		order.append(" select top 1 @next = 1 from @orders;");
		
		order.append(" if @outputOrdersExistOnly = 1 begin");
		order.append(" select @next;");
		order.append(" return;");
		order.append(" end");
		db.executeSql(order);
		
		
		String ymtOrders = " select iOrderId, isnull(iTradingId,iMainOrderId) as iTradingId, iUserId, sReceivePerson, sAddress, sLeaveWord, sTelephone, "
				+ " iTradingStatus, iRiskVerifiedStatus, cast(1 as bit) as bPaidInFull, sBuyerLoginId, sEmail, fFreight, dAddTime, "
				+ " sPhone, sPostCode,sBuyerLoginEmail"
				+ " from Ymt_Orders(nolock) where iOrderId in (select id from @orders);";
		
		List<Map> orders = db.executeSql(order.toString()+ymtOrders);
		result.put("orders", orders);
		
		int next = (int)db.executeSql(order.toString()+" select @next as [next];").get(0).get("next");
		
		if(next > 0){
			String ymtOrderInfo = " select iOrderId, iBondedArea, iAmount, fOriginalPrice, sTitle,"
					+ " sProductCode, sProductId, sSKU, sCatalogId, sPropertyInfo"
					+ " from Ymt_OrderInfo(nolock) where iOrderId in (select id from @orders)"
					+ " and (@considerBondedArea != 1 or @considerBondedArea = 1 and iBondedArea = @bondedArea);";
			List<Map> orderInfos = db.executeSql(order.toString()+ymtOrderInfo);
			result.put("orderInfos", orderInfos);
			
			String ymtTradingItem = " select iOrderId, iTradingId, dUpdateTime from Ymt_TradingItem(nolock)"
					+ " where iOrderId in (select id from @orders);";
			List<Map> tradingItems = db.executeSql(order.toString()+ymtTradingItem);
			result.put("tradingItems", tradingItems);
			
			String ymtTradingInfo = " select iTradingId, iTradingStatus, sPayChannel from Ymt_TradingInfo(nolock)"
					+ " where iTradingId in (select iTradingId from Ymt_TradingItem(nolock) "
					+ " where iOrderId in (select id from @orders));";
			List<Map> tradingInfos = db.executeSql(order.toString()+ymtTradingInfo);
			result.put("tradingInfos", tradingInfos);
			
			String ymtOrderState = " select iOrderId, fPaidAmountOfCash, fPostPaidAmountOfCash,"
					+ " fRefundedAmountOfCash from Ymt_OrderState(nolock) where iOrderId in (select id from @orders);";
			List<Map> orderStates = db.executeSql(order.toString()+ymtOrderState);
			result.put("orderStates", orderStates);
			
			String ymtUsers = " select 0 as iUserId, '' as sPhone,'' as sLoginEmail;";
			List<Map>  users = db.executeSql(order.toString()+ymtUsers);
			result.put("users", users);
			
			String ymtIdPic = " select o.id as iOrderId, p.iUserId, p.sName, p.sCardId"
					+ " from @orders o join ymt_idpic(nolock) p on o.[uid] = p.iUserId and o.receiver = p.sName";
			List<Map> pics = db.executeSql(order.toString()+ymtIdPic);
			result.put("pics", pics);
			
			String t1 = "; with t as("
					+ " select ROW_NUMBER() over(partition by iOrderId order by dAddTime desc) as n,"
					+ " iOrderId, dAddTime, sContent from Ymt_O_OrderNote(nolock) where iOrderId in (select id from @orders)"
					+ " ) ";
			t1 += " select iOrderId, dAddTime, sContent from t where n = 1;";
			List<Map> orderNotes = db.executeSql(order.toString()+t1);
			result.put("orderNotes", orderNotes);
			
			String t2 = "; with t as("
					+ " select ROW_NUMBER() over(partition by iOrderId order by dAddTime desc) as n,"
					+ " iOrderId, sLogisticsProvider from Ymt_OrderSummary(nolock) where iOrderId in (select id from @orders)"
					+ " ) ";
			t2 += " select iOrderId, sLogisticsProvider from t where n = 1;";
			List<Map> orderSummaries = db.executeSql(order.toString()+t2);
			result.put("orderSummaries", orderSummaries);
		}
		return result;
	}

	public static void main(String[] args) {
		Sp_GetBondedProductInfo sp = new Sp_GetBondedProductInfo();
		sp.setSellerId(3383);
		sp.execute();
	}
}
